
/*
+----------------------------------------------------------------------------+
!                             FICHA TECNICA DO PROGRAMA                      !
+----------------------------------------------------------------------------+
!Programa          ! REI44A13                                                !
+------------------+---------------------------------------------------------+
!Parametros        ! mvCodProj (C) - Codigo do Projeto                       !
!                  ! mvTipo (C)    - Tipo do Saldo (P-Previsto/R-Realizado)  !
!                  ! mvImposto (C) - Tipo do Imposto (ICMS/IPI/ISS/PIS/COF)  !
+------------------+---------------------------------------------------------+
!Descricao         ! DRE Excel - Retorno dos valores de impostos             !
+------------------+---------------------------------------------------------+
!Autor             ! TSC195-Gustavo Schepp                                   !
+------------------+---------------------------------------------------------+
!Data de Criacao   ! 07/12/10                                                !
+------------------+---------------------------------------------------------+
!   ATUALIZACOES                                                             !
+-------------------------------------------+-----------+-----------+--------+
!   Descricao detalhada da atualizacao      !Nome do    ! Analista  !Data da !
!                                           !Solicitante! Respons.  !Atualiz.!
+-------------------------------------------+-----------+-----------+--------+
!                                           !           !           !        !
!                                           !           !           !        !
+-------------------------------------------+-----------+-----------+--------+ 
*/

User Function REI44A13(mvCodProj,mvTipo,mvImposto,mvTpVen)
	Local _nVlrRet := 0
	Local _cQuery
	// area inicial
	Local _aAreaAnt := GetArea()
	
	if empty(mvTpVen)
		mvTpVen := "P"  // produto
	endif
	
	/*
	// IMPOSTOS - REALIZADO
	If (mvTipo=="R")
		// monta a query
		_cQuery := "SELECT "
		// valor dos impostos
		If (mvImposto=="ICMS") // ICMS
			_cQuery += "SUM(F2_VALICM)"
		ElseIf (mvImposto=="IPI") // IPI
			_cQuery += "SUM(F2_VALIPI)"
		ElseIf (mvImposto=="ISS") // ISS
			_cQuery += "SUM(F2_VALISS)"
		ElseIf (mvImposto=="COF") // COFINS
			_cQuery += "SUM(F2_VALIMP5)"
		ElseIf (mvImposto=="PIS") // PIS
			_cQuery += "SUM(F2_VALIMP6)"
		EndIf
		_cQuery += " IT_VLRIMP "
		// nota de saida
		_cQuery += "FROM "+RetSqlName("SF2")+" SF2 "
		_cQuery += "WHERE F2_FILIAL = '"+xFilial("SF2")+"' AND SF2.D_E_L_E_T_ = ' ' "
		_cQuery += "AND F2_DOC || F2_SERIE || F2_CLIENTE || F2_LOJA IN "
		// itens liberados de pedidos
		_cQuery += "     ( "
		_cQuery += "     SELECT DISTINCT C9_NFISCAL || C9_SERIENF || C9_CLIENTE || C9_LOJA "
		_cQuery += "     FROM "+RetSqlName("SC9")+" SC9 "
		_cQuery += "     WHERE C9_FILIAL = '"+xFilial("SC9")+"' AND SC9.D_E_L_E_T_ = ' ' "
		_cQuery += "     AND C9_BLEST = '10' AND C9_BLCRED = '10' "
		_cQuery += "     AND C9_PEDIDO IN "
		// eventos financeiros
		_cQuery += "          ( "
		_cQuery += "          SELECT ZJ1_PEDIDO "
		_cQuery += "          FROM "+RetSqlName("ZJ1")+" ZJ1 "
		_cQuery += "          WHERE ZJ1_FILIAL = '"+xFilial("ZJ1")+"' AND ZJ1.D_E_L_E_T_ = ' ' "
		_cQuery += "          AND ZJ1_PROJET = '"+mvCodProj+"' "
		_cQuery += "          ) "
		_cQuery += "     ) "

memowrit("\cprova\REI44A13_"+mvImposto+".txt",_cQuery)

	// IMPOSTOS - PREVISTO
	ElseIf (mvTipo=="R")
		Return(0)

	EndIf

	// fecha alias
	If (Select("_QRYIMP")<>0)
		dbSelectArea("_QRYIMP")
		dbCloseArea()
	EndIf

	dbUseArea(.T.,"TOPCONN",TCGENQRY(,,_cQuery),"_QRYIMP",.F.,.T.)
	dbSelectArea("_QRYIMP")

	// variavel de retorno
	_nVlrRet := _QRYIMP->IT_VLRIMP

	// fecha alias da query
	dbSelectArea("_QRYIMP")
	dbCloseArea()

	// restaura area inicial
	RestArea(_aAreaAnt)

Return(_nVlrRet)
*/


// calculo de impostos sobre produtos, alterado em 30/01/14 por Eliane
// solicitacao do sr. Gerson
_cQuery := " SELECT "
		// valor dos impostos
If (mvImposto=="ICMS") // ICMS
	_cQuery += " SUM(D2_VALICM) "
ElseIf (mvImposto=="IPI") // IPI
	_cQuery += " SUM(D2_VALIPI) "
ElseIf (mvImposto=="COF") // COFINS
	_cQuery += " SUM(D2_VALIMP5) "
ElseIf (mvImposto=="PIS") // PIS
	_cQuery += " SUM(D2_VALIMP6) "
ElseIf (mvImposto=="ISS") // ISS
	_cQuery += " SUM(D2_VALISS) "
EndIf
_cQuery += " IT_VLRIMP "
_cQuery += " FROM "+RetSqlName("SD2")+" SD2 "
_cQuery += " INNER JOIN "+RetSqlName("SF4")+" SF4 "
_cQuery += "      ON ( F4_FILIAL = '"+xFilial("SF4")+"' "
_cQuery += "		AND F4_CODIGO = D2_TES )
_cQuery += " INNER JOIN "+RetSqlName("ZJ1")+" ZJ1 "
_cQuery += "      ON ( ZJ1_FILIAL = '"+xFilial("ZJ1")+"' "
_cQuery += " 		AND ZJ1_PEDIDO = D2_PEDIDO  AND SUBSTR(ZJ1_NUMSEQ,2,2) = D2_ITEM ) "
_cQuery += " WHERE D2_FILIAL = '"+xFilial("SD2")+"' "

// imposto sobre produto ou servico
if mvTpVen == "P"
	_cQuery += " AND F4_ISS <> 'S' "
else
	_cQuery += " AND F4_ISS = 'S' "
endif

_cQuery += " AND ZJ1_PROJET = '"+mvCodProj+"' "
_cQuery += " AND SD2.D_E_L_E_T_ = ' ' "
_cQuery += " AND ZJ1.D_E_L_E_T_ = ' ' "
_cQuery += " AND SF4.D_E_L_E_T_ = ' ' "

memowrit("\cprova\REI44A13_"+mvImposto+".txt",_cQuery)
	
		// fecha alias
If (Select("_QRYIMP")<>0)
	dbSelectArea("_QRYIMP")
	dbCloseArea()
EndIf

dbUseArea(.T.,"TOPCONN",TCGENQRY(,,_cQuery),"_QRYIMP",.F.,.T.)
dbSelectArea("_QRYIMP")

	// variavel de retorno
_nVlrRet := _QRYIMP->IT_VLRIMP

	// fecha alias da query
dbSelectArea("_QRYIMP")
dbCloseArea()

// subtrai as devolucoes de vendas
_cQuery := " SELECT "
		// valor dos impostos
If (mvImposto=="ICMS") // ICMS
	_cQuery += " SUM(D1_VALICM) "
ElseIf (mvImposto=="IPI") // IPI
	_cQuery += " SUM(D1_VALIPI) "
ElseIf (mvImposto=="COF") // COFINS
	_cQuery += " SUM(D1_VALIMP5) "
ElseIf (mvImposto=="PIS") // PIS
	_cQuery += " SUM(D1_VALIMP6) "
ElseIf (mvImposto=="ISS") // ISS
	_cQuery += " SUM(D1_VALISS) "
EndIf
_cQuery += " IT_VLRIMP "
_cQuery += " FROM "+RetSqlName("SD1")+" SD1 "
_cQuery += " INNER JOIN "+RetSqlName("SD2")+" SD2 "
_cQuery += " ON (D2_FILIAL = D1_FILIAL AND D2_DOC=D1_NFORI AND D2_SERIE=D1_SERIORI AND D2_ITEM=D1_ITEMORI) "

_cQuery += " INNER JOIN "+RetSqlName("SF4")+" SF4 "
_cQuery += "      ON ( F4_FILIAL = '"+xFilial("SF4")+"' "
_cQuery += "		AND F4_CODIGO = D2_TES )
_cQuery += " INNER JOIN "+RetSqlName("ZJ1")+" ZJ1 "
_cQuery += "      ON ( ZJ1_FILIAL = '"+xFilial("ZJ1")+"' "
_cQuery += " 		AND ZJ1_PEDIDO = D2_PEDIDO  AND SUBSTR(ZJ1_NUMSEQ,2,2) = D2_ITEM ) "
_cQuery += " WHERE D1_FILIAL = '"+xFilial("SD1")+"' "
_cQuery += " AND D1_TIPO = 'D' "

// imposto sobre produto ou servico
if mvTpVen == "P"
	_cQuery += " AND F4_ISS <> 'S' "
else
	_cQuery += " AND F4_ISS = 'S' "
endif

_cQuery += " AND ZJ1_PROJET = '"+mvCodProj+"' "
_cQuery += " AND SD2.D_E_L_E_T_ = ' ' "
_cQuery += " AND ZJ1.D_E_L_E_T_ = ' ' "
_cQuery += " AND SF4.D_E_L_E_T_ = ' ' "
_cQuery += " AND SD1.D_E_L_E_T_ = ' ' "

memowrit("\cprova\REI44A13_"+mvImposto+"_DEVOL.txt",_cQuery)
	
		// fecha alias
If (Select("_QRYIMP")<>0)
	dbSelectArea("_QRYIMP")
	dbCloseArea()
EndIf

dbUseArea(.T.,"TOPCONN",TCGENQRY(,,_cQuery),"_QRYIMP",.F.,.T.)
dbSelectArea("_QRYIMP")

// variavel de retorno, subtrai devolucao
_nVlrRet -= _QRYIMP->IT_VLRIMP

	// fecha alias da query
dbSelectArea("_QRYIMP")
dbCloseArea()

// restaura area inicial
RestArea(_aAreaAnt)

Return(_nVlrRet)
